﻿using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Threading.Tasks;
using VOL.Core.DBManager;
using VOL.Core.Services;
using VOL.Entity.DomainModels;
using VOL.MyTable.IRepositories;
using VOL.MyTable.Repositories;

namespace VOL.WebApi.Controllers.MyTable
{
    /// <summary>
    /// 费控系统的后台task API管理
    /// </summary>
    [Route("api/Fk_Task")]
    public class Fk_TaskController : Controller
    {

        /// <summary>
        /// 取SAP接口的执行数据
        /// 每天中午12点，晚上22点执行
        /// 12 0 12,22 * * ? *
        /// </summary>
        [HttpPost, Route("get_sap_sjfs"), AllowAnonymous]
        public void get_sap_sjfs()
        {
            //每次取中间表都是全部数据，默认为中间表是当月的全部数据
            string[] dateFormates = { "yyyy-MM-dd", "MM/yyyy/dd", "yyyyMMdd", "yyyy/MM/dd", "yyyy/M/dd", "yyyy/M/d", "M/d/yyyy h/mm/ss", "M/d/yyyy h:mm:ss tt", "M/d/yyyy" };
            string sql = "SELECT * FROM [117]..[JIRUI].[COST_MANAGEMENT_EXPENSE] a";//实际发生额，通过DBLINK直接在SQLServer上运行。
            dynamic[] array_sap = DBServerProvider.SqlDapper.QueryList<object>(sql, null).ToArray();

            //1、删除本月的所有数据；
            //2、把中间库的数据插入；

            //return;//调试期间不操作；
            IQueryable<Fk_sjfs> _query = Fk_sjfsRepository.Instance.FindAsIQueryable(t => (t.cdate.Value.Month == DateTime.Now.Month) &&(t.ls_type=="auto"));
            //IQueryable<Fk_sjfs> _query = Fk_sjfsRepository.Instance.FindAsIQueryable(t => (1==1));
            
            DbSet<Fk_sjfs> Fk_sjfs_all = DBServerProvider.DbContext.Set<Fk_sjfs>();
            Fk_sjfs_all.RemoveRange(_query);//删除本月数据且是自动插入的数据

            DBServerProvider.DbContext.SaveChanges();

            IFk_sjfsRepository _rep = Fk_sjfsRepository.Instance;
            Fk_sjfs _tt = new Fk_sjfs();//用于调试发现是哪条数据的问题；

            try
            {    
                    foreach (dynamic item in array_sap)
                    {
                        Fk_sjfs _ls = new Fk_sjfs();
                        _ls.budat = DateTime.ParseExact(item.BUDAT, dateFormates, CultureInfo.InvariantCulture, DateTimeStyles.None);//日期格式转换
                        //_ls.budat = DateTime.ParseExact(item.CDATE, dateFormates, CultureInfo.InvariantCulture, DateTimeStyles.None);//日期格式转换
                        _ls.docnr = item.DOCNR;
                        _ls.drcrk = item.DRCRK;
                        _ls.hsl = item.HSL;
                        _ls.racct = item.RACCT;
                        _ls.rcntr = item.RCNTR;
                        _ls.remark = item.BKTXT;
                        _ls.fytype = item.FTYPE;
                        _ls.ls_type = "auto";//auto说明是从SAP传过来的数据
                        _ls.cdate = DateTime.ParseExact(item.CDATE, dateFormates, CultureInfo.InvariantCulture, DateTimeStyles.None);//日期格式转换;
                        _tt = _ls;
                        _rep.Add(_ls, true);
                    }
            }
            catch(Exception ex)
            {
                Logger.Error("SAP实际发生额，写入错误："+ _tt.budat+","+_tt.docnr+","+_tt.drcrk+","+_tt.hsl.ToString()+","+_tt.racct+","+_tt.rcntr+","+_tt.remark+"===="+ex.Message+ex.StackTrace);
            }

            get_sap_cntro_project();//同步成本中心、项目订单
            get_sap_accountant();//同步SAP科目
        }

        /// <summary>
        /// 计算预算的可用额度
        /// 没隔1小时执行一次
        /// 0 0 * * * ? *
        /// </summary>
        [HttpPost, Route("calc_kyed"), AllowAnonymous]
        public void calc_kyed()
        {
            //月度累计可用额度=月底累计预算合计-SAP的实际发生额合计-流程中的发生额合计（Fk_reim.stepid！=9）
            //年度可用额度=今年的预算合计-SAP的实际发生额合计-流程中的发生额的合计（Fk_reim.stepid！=9），年度暂时不做

            IQueryable<Fk_yusuan_view_unpivot> ys_z = DBServerProvider.DbContext.Set<Fk_yusuan_view_unpivot>()
                .Where(s=>s.gjahr == DateTime.Now.Year && s.yue<=DateTime.Now.Month);

            IQueryable<Fk_sjfs> sum_sjfs = DBServerProvider.DbContext.Set<Fk_sjfs>()
                .Where(w=>w.budat.Value.Year==DateTime.Now.Year);//全年累计实际发生额from SAP

            IQueryable<Fk_reim> sum_reim = DBServerProvider.DbContext.Set<Fk_reim>()
                .Where(w => w.stepid != 9);//9，完成流程表示流程归档

            IQueryable<Fk_reimlist> sum_reimlist = DBServerProvider.DbContext.Set<Fk_reimlist>()
                .Where(w =>w.fzdate.Value.Year== DateTime.Now.Year);//流程中发生额

            var sum_ys = from p1 in ys_z//预算值
                         where p1.gjahr == DateTime.Now.Year &&  p1.yue <= DateTime.Now.Month
                         group p1 by new { p1.racct, p1.rcntr } into g
                         select new kyed_struct
                         {
                             racct = g.Key.racct,
                             rcntr=g.Key.rcntr,
                             je=g.Sum(s=>s.je??0)
                         };

            var sum_sj = from p1 in sum_sjfs//实际发生
                         where p1.budat.Value.Year == DateTime.Now.Year
                         group p1 by new { p1.racct, p1.rcntr } into g
                         select new kyed_struct
                         {
                             racct = g.Key.racct,
                             rcntr = g.Key.rcntr,
                             je = g.Sum(s => s.hsl??0)
                         };
            var s_reimlist = from p1 in sum_reimlist//合计是明细表fk_reimlist中的金额
                             from p2 in sum_reim
                             where p1.fzdate.Value.Year == DateTime.Now.Year &&p1.reim_id ==p2.reim_id &&p2.stepid != 9//合计没有归档的数据，9表示归档
                             group p1 by new { racct= p1.fei_type, rcntr=p1.rcntr } into g
                         select new kyed_struct
                         {
                             racct = g.Key.racct,
                             rcntr = g.Key.rcntr,
                             je = g.Sum(s => s.je)
                         };
            List<kyed_struct> yus_list = sum_ys.ToList();//先计算出来,防止后面的循环多次访问数据库
            List<kyed_struct> sj_list = sum_sj.ToList();//先计算出来
            List<kyed_struct> reim_list = s_reimlist.ToList();//先计算出来

            using (var db= DBServerProvider.DbContext) {
                DbSet<Fk_yusuan> yus_tb = db.Set<Fk_yusuan>();
                foreach (Fk_yusuan item in yus_tb)
                {
                    //db.dbAttach(item);
                    if (item.gjahr == DateTime.Now.Year )//只更新今年的预算可用额度,逐条更新
                    {
                        yus_tb.Attach(item);//更新、删除数据库时使用Attach可以减少数据库的访问，提升性能。
                        decimal ys = yus_list.Where(w => w.racct == item.racct && w.rcntr == item.rcntr).Select(s => s.je).Sum(s => s.Value);
                        decimal sj= sj_list.Where(w => w.racct == item.racct && w.rcntr == item.rcntr).Select(s => s.je).Sum(s => s.Value);
                        decimal lc= reim_list.Where(w => w.racct == item.racct && w.rcntr == item.rcntr).Select(s => s.je).Sum(s => s.Value);
                        item.kyed = ys - sj - lc;
                    }
                  
                }
                db.SaveChanges();//注意不能放在foreach循环里面
            }
            //IQueryable<Yus_Analyse> sum_sjfs = null;//全年累计实际发生额from SAP
           

        }

        /// <summary>
        /// 成本中心和项目订单主数据同步，
        /// 同步逻辑：
        /// 只能增量传，因为成本中心主数据有授权部门子表
        /// </summary>
       // [HttpPost, Route("get_sap_cntro_project"), AllowAnonymous]
        private void get_sap_cntro_project()
        {

            DbSet<Fk_cntr> Fk_cntr_all = DBServerProvider.DbContext.Set<Fk_cntr>();

            string[]? rcntr_array = Fk_cntr_all.Select(t => t.rcntr_no).ToArray();

            //string[] dateFormates = { "yyyy-MM-dd", "MM/yyyy/dd", "yyyyMMdd", "yyyy/MM/dd", "yyyy/M/dd", "yyyy/M/d", "M/d/yyyy h/mm/ss", "M/d/yyyy h:mm:ss tt", "M/d/yyyy" };
            string sql = "SELECT * FROM [117]..[JIRUI].[COST_CENTER] a";//成本中心/项目订单，通过DBLINK直接在SQLServer上运行。
            dynamic[] array_sap_all = DBServerProvider.SqlDapper.QueryList<object>(sql, null).ToArray();
            dynamic[] array_sap= array_sap_all.Where(t =>(!rcntr_array.Contains((string)t.RCNTR))).ToArray();//查出新增的成本中心主数据
           // return array_sap;
           

            IFk_cntrRepository _rep = Fk_cntrRepository.Instance;
            Fk_cntr _tt = new Fk_cntr();//

            try
            {
                foreach (dynamic item in array_sap)
                {
                    Fk_cntr _ls = new Fk_cntr();
                    //*//* _ls.budat = DateTime.ParseExact(item.BUDAT, dateFormates, CultureInfo.InvariantCulture, DateTimeStyles.None);//日期格式转换
                    _ls.rcntr_no = item.RCNTR;
                    _ls.rcntr = item.KTEXT;
                    _ls.company_id = Convert.ToInt32(item.BUKRS);
                    _ls.fzren = item.VERAK;
                    _ls.lstype = item.FTYPE;
                    _tt = _ls;
                    _rep.Add(_ls, true);
                }

            }
            catch (Exception ex)
            {
                Logger.Error("成本中心，写入错误：" + _tt.rcntr_no +"====" + ex.Message + ex.StackTrace);
            }
            //return array_sap;
        }


        /// <summary>
        /// SAP科目主数据同步，
        /// 同步逻辑：
        /// 科目增量传   只传9000的科目
        /// </summary>
        // [HttpPost, Route("get_sap_cntro_project"), AllowAnonymous]
        private void get_sap_accountant()
        {

            DbSet<Fei_typelist> Fei_typelist_all = DBServerProvider.DbContext.Set<Fei_typelist>();

            string[]? racct_array = Fei_typelist_all.Select(t => t.racct).ToArray();

            //string[] dateFormates = { "yyyy-MM-dd", "MM/yyyy/dd", "yyyyMMdd", "yyyy/MM/dd", "yyyy/M/dd", "yyyy/M/d", "M/d/yyyy h/mm/ss", "M/d/yyyy h:mm:ss tt", "M/d/yyyy" };
            string sql = "SELECT * FROM [117]..[JIRUI].[ACCOUNTANT_COURSE] a where bukrs='9000'";//科目，通过DBLINK直接在SQLServer上运行。
            dynamic[] array_sap_all = DBServerProvider.SqlDapper.QueryList<object>(sql, null).ToArray();
            dynamic[] array_sap = array_sap_all.Where(t => (!racct_array.Contains((string)t.RACCT))).ToArray();//查出新增的科目主数据
                                                                                                               // return array_sap;
            //IFei_typelistRepository _rep = Fei_typelistRepository.Instance;
            
            Fei_typelist _tt = new Fei_typelist();//

            try
            {
                using (var db = DBServerProvider.DbContext)
                {
                    DbSet<Fei_typelist> _rep = db.Set<Fei_typelist>();

                    foreach (dynamic item in array_sap)
                    {
                        Fei_typelist _ls = new Fei_typelist();
                        //*//* _ls.budat = DateTime.ParseExact(item.BUDAT, dateFormates, CultureInfo.InvariantCulture, DateTimeStyles.None);//日期格式转换
                        _ls.racct = item.RACCT;
                        _ls.fei_typename = item.TXT50;
                        _ls.company_oa_id = Convert.ToInt32(item.BUKRS);
                        _tt = _ls;
                         _rep.Update(_ls);
                    }
                    db.SaveChanges();////注意不能放在foreach循环里面
                }
            }
            catch (Exception ex)
            {
                Logger.Error("科目，写入错误：" + _tt.racct + "====" + ex.Message + ex.StackTrace);
            }
            //return array_sap;
        }

        public class kyed_struct//可用额度返回结构
        {
            public string racct { get; set; }
            public string rcntr { get; set; }
            public decimal? je { get; set; }
        }

    }
}
